package quary;

public class QuaryBridge {

	private Quary quary = new Quary(VinylDataVar.DB, VinylDataVar.USERNAME, VinylDataVar.PASSWORD);
	
	public Object[][] getVinylData(int vinylID) {
		String[] variables = {
				VinylDataVar.VINYL_ARTIST,
				VinylDataVar.VINYL_ALBUM,
				VinylDataVar.VINYL_MUSIC_STYLE,
				VinylDataVar.VINYL_FORMAT,
				VinylDataVar.VINYL_LABEL
		};
		
		String[] from = {
				VinylDataVar.VINYL
		};
		
		String keyVar = VinylDataVar.VINYL_ID;
		
		int key = vinylID;
		
		String selectionQuary = quary.buildSelectQuary(variables, from, null, keyVar, key, null, null);
		
		Object[][] data = quary.getSelectionData(selectionQuary);
		
		return data;
	}
	
	public Object[][] getMemberData(int memberID) {
		String[] variables = {
				VinylDataVar.PERSON_FIRST_NAME,
				VinylDataVar.PERSON_MIDDLE_NAME,
				VinylDataVar.PERSON_LAST_NAME,
				VinylDataVar.PERSON_ADDRESS,
				VinylDataVar.PERSON_ZIP,
				VinylDataVar.PERSON_LOCATION,
				VinylDataVar.PERSON_PHONE,
				VinylDataVar.PERSON_EMAIL
		};
		
		String[] from = {
				VinylDataVar.MEMBER,
				VinylDataVar.PERSON
		};
		
		String[][] on = {
				{VinylDataVar.MEMBER_PERSON_ID, VinylDataVar.PERSON_ID}
		};
		
		String keyVar = VinylDataVar.MEMBER_ID;
		
		int key = memberID;
		
		int[][] varMerge = {
				{0,1,2}
		};

		String selectionQuary = quary.buildSelectQuary(variables, from, on, keyVar, key, null, varMerge);
		
		Object[][] data = quary.getSelectionData(selectionQuary);
		
		return data;
	}
	
	public Object[][] getStorageData(int storageID) {
		//Find out if this storage is at a member or in a clubhouse
		String[] variables = {
				VinylDataVar.STORAGE_AT_MEMBER
		};
		
		String[] from = {
				VinylDataVar.STORAGE
		};
		
		String[][] on = null;
		
		String keyVar = VinylDataVar.STORAGE_ID;
		
		int key = storageID;
		
		String orderBy = null;
		
		int[][] varMerge = null;
		
		String selectionQuary = quary.buildSelectQuary(variables, from, on, keyVar, key, orderBy, varMerge);
		
		Object[][] data = quary.getSelectionData(selectionQuary);
		
		//At Member
		if (data[0][0] != null) {
			variables = new String[] {
					VinylDataVar.PERSON_FIRST_NAME,
					VinylDataVar.PERSON_MIDDLE_NAME,
					VinylDataVar.PERSON_LAST_NAME,
					VinylDataVar.PERSON_ADDRESS,
					VinylDataVar.PERSON_ZIP,
					VinylDataVar.PERSON_LOCATION,
					VinylDataVar.PERSON_PHONE,
					VinylDataVar.PERSON_EMAIL,
					VinylDataVar.STORAGE_DESCRIPTION
			};
			
			from = new String[] {
					VinylDataVar.STORAGE,
					VinylDataVar.MEMBER,
					VinylDataVar.PERSON
			};
			
			on = new String[][] {
					{VinylDataVar.MEMBER_ID, VinylDataVar.STORAGE_AT_MEMBER},
					{VinylDataVar.MEMBER_PERSON_ID, VinylDataVar.PERSON_ID}
			};
			
			keyVar = VinylDataVar.STORAGE_ID;
			
			key = storageID;
			
			varMerge = new int[][]{{0,1,2}};
		} 
		
		//At Clubhouse
		else {
			variables = new String[] {
					VinylDataVar.CLUBHOUSE_NAME,
					VinylDataVar.CLUBHOUSE_ADDRESS,
					VinylDataVar.CLUBHOUSE_ZIP,
					VinylDataVar.CLUBHOUSE_LOCATION,
					VinylDataVar.CLUBHOUSE_PHONE,
					VinylDataVar.STORAGE_DESCRIPTION,
			};
			
			from = new String[] {
					VinylDataVar.STORAGE,
					VinylDataVar.CLUBHOUSE,
			};
			
			on = new String[][] {
					{VinylDataVar.CLUBHOUSE_ID, VinylDataVar.STORAGE_IN_CLUBHOUSE},
			};
			
			keyVar = VinylDataVar.STORAGE_ID;
			
			key = storageID;	
		}
		
		selectionQuary = quary.buildSelectQuary(variables, from, on, keyVar, key, orderBy, varMerge);
		
		data = quary.getSelectionData(selectionQuary);

		return data;
	}
	
	public Object[][] getStorageAccessData(int storageID) {
		String[] variables = {
				VinylDataVar.PERSON_FIRST_NAME,
				VinylDataVar.PERSON_MIDDLE_NAME,
				VinylDataVar.PERSON_LAST_NAME,
				VinylDataVar.PERSON_PHONE,
				VinylDataVar.PERSON_EMAIL,
				VinylDataVar.MEMBER_POSITION
		};
		
		String from[] = {
				VinylDataVar.STORAGE,
				VinylDataVar.ACCESS,
				VinylDataVar.MEMBER,
				VinylDataVar.PERSON
		};
		
		String on[][] = {
				{VinylDataVar.STORAGE_ID, VinylDataVar.ACCESS_STORAGE_ID},
				{VinylDataVar.ACCESS_MEMBER_ID, VinylDataVar.MEMBER_ID},
				{VinylDataVar.MEMBER_PERSON_ID, VinylDataVar.PERSON_ID}
		};
		
		String keyVar = VinylDataVar.STORAGE_ID;
		
		int key = storageID;
	
		int[][] varMerge = {
				{0,1,2}
		};

		String selectionQuary = quary.buildSelectQuary(variables, from, on, keyVar, key, null, varMerge);
		
		Object[][] data = quary.getSelectionData(selectionQuary);

		return data;
	
	}

	public Object[][] getPersonData(int personID) {
		String[] variables = {
				VinylDataVar.PERSON_FIRST_NAME,
				VinylDataVar.PERSON_MIDDLE_NAME,
				VinylDataVar.PERSON_LAST_NAME,
				VinylDataVar.PERSON_ADDRESS,
				VinylDataVar.PERSON_ZIP,
				VinylDataVar.PERSON_LOCATION,
				VinylDataVar.PERSON_PHONE,
				VinylDataVar.PERSON_EMAIL
		};
		
		String[] from = {
				VinylDataVar.PERSON
		};
		
		String[][] on = null;
		
		String keyVar = VinylDataVar.PERSON_ID;
		
		int key = personID;
		
		int[][] varMerge = {
				{0,1,2}
		};

		String selectionQuary = quary.buildSelectQuary(variables, from, on, keyVar, key, null, varMerge);
		
		Object[][] data = quary.getSelectionData(selectionQuary);
		
		return data;
	}
	
	public Object[][] getRentedList() {
		String[] variables = {
				VinylDataVar.VINYL_ID,
				VinylDataVar.VINYL_ARTIST,
				VinylDataVar.VINYL_ALBUM,
				VinylDataVar.VINYL_DELIVERY_DATE,
				VinylDataVar.VINYL_OWNER,
				VinylDataVar.PERSON_FIRST_NAME,
				VinylDataVar.PERSON_MIDDLE_NAME,
				VinylDataVar.PERSON_LAST_NAME
		};
		
		String[] from = {
				VinylDataVar.VINYL,
				VinylDataVar.PERSON
		};
		
		String[][] on = {
				{VinylDataVar.VINYL_OWNER, VinylDataVar.PERSON_ID}
		};
		
		String orderBy = VinylDataVar.VINYL_ID;
		
		String selectionQuary = quary.buildSelectQuary(variables, from, on, null, 0, orderBy, null);
		
		return quary.getSelectionData(selectionQuary);
	}

	public Object[][] getLoansList() {
		String[] variables = {
				VinylDataVar.VINYL_ID,
				VinylDataVar.VINYL_ARTIST,
				VinylDataVar.VINYL_ALBUM,
				VinylDataVar.LOANS_LOAN_DATE,
				VinylDataVar.LOANS_DELIVERY_DATE,
				VinylDataVar.MEMBER_ID,
		};
		
		String[] from = {
				VinylDataVar.LOANS,
				VinylDataVar.MEMBER,
				VinylDataVar.VINYL
		};
		
		String[][] on = {
				{VinylDataVar.LOANS_MEMBER_ID, VinylDataVar.MEMBER_ID},
				{VinylDataVar.VINYL_ID, VinylDataVar.LOANS_VINYL_ID}		
		};
		
		String orderBy = VinylDataVar.VINYL_ID;
		
		String selectionQuary = quary.buildSelectQuary(variables, from, on, null, 0, orderBy, null);

		return (quary.getSelectionData(selectionQuary));
	}

	public Object[][] getVinylList() {
		String[] variables = {
				VinylDataVar.VINYL_ID,
				VinylDataVar.VINYL_ARTIST,
				VinylDataVar.VINYL_ALBUM,
				VinylDataVar.VINYL_MUSIC_STYLE,
				VinylDataVar.VINYL_FORMAT,
				VinylDataVar.VINYL_LABEL,
				VinylDataVar.STORAGE
		};
		
		String[] from = {
				VinylDataVar.VINYL,
				VinylDataVar.STORAGE
		};
		
		String[][] on = {
				{VinylDataVar.VINYL_STORAGE, VinylDataVar.STORAGE_ID},
		};
		
		String orderBy = VinylDataVar.VINYL_ID;
		
		String selectionQuary = quary.buildSelectQuary(variables, from, on, null, 0, orderBy, null);
		
		return quary.getSelectionData(selectionQuary);
	}
	
	public int deleteFromDatabase(String table, String keyVar, Object[] keyArray) {
		String deleteQuary = quary.buildDeleteQuary(table, keyVar, keyArray);
		
		return quary.deleteFromDatabase(deleteQuary);
	}
	
	public int insertIntoDatabase(String table, String[] variables, Object[] data) {
		String insertQuary = quary.buildInsertQuary(table, variables, data);
			
		return quary.insertIntoDatabase(insertQuary);
	}
	
	public int updateRowInDatabase(String table, String[] variables, Object[] data, String keyVar, Object[] keyArray) {
		String updateQuary = quary.buildUpdateQuary(table, variables, data, keyVar, keyArray);
		
		return quary.updateDatabase(updateQuary);
	}
	
	public Object[][] getTableData(String table, String orderBy) {
		String[] variables = {"*"};
		
		String[] from = {table};
		
		String selectionQuary = 
			quary.buildSelectQuary(variables, from, null, null, 0, orderBy, null);
	
		return quary.getSelectionData(selectionQuary);
	}
	
	public Object[] getKeysFrom(String table, String var) {
		String[] variables = {var};
		String[] from = {table};
		
		String selectionQuary = 
			quary.buildSelectQuary(variables, from, null, null, 0, null, null);
		
		Object[][] data = quary.getSelectionData(selectionQuary);
		
		Object[] keys = new Object[data.length +1];
		keys[0] = Quary.NULL;
				
		for (int i = 1; i < data.length +1; i++) {
			keys[i] = data[i -1][0];
		}
		
		return keys;
	}
}
	
	
	
	
	

